﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Dapper;
using System.Linq;
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
using Gx.Model.ViewModel;

namespace Gx.Dal.Manage
{
    /// <summary>
    /// 
    /// </summary>
    public partial class B_EmployeeInfoDal : SqlServerHelper
    {
        public int AddB_EmployeeInfo(B_EmployeeInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO B_EmployeeInfo");
            strSql.Append("(");
            strSql.Append("CompanyId,");
            strSql.Append("CompanyName,");
            strSql.Append("CityId,");
            strSql.Append("EmpNum,");
            strSql.Append("EmpName,");
            strSql.Append("EmpPwd,");
            strSql.Append("EmpCard,");
            strSql.Append("EmpBirthday,");
            strSql.Append("EmpSex,");
            strSql.Append("EmpAge,");
            strSql.Append("EmpCulture,");
            strSql.Append("EmpMobile,");
            strSql.Append("UrgentName,");
            strSql.Append("UrgentMobile,");
            strSql.Append("EmpProvince,");
            strSql.Append("EmpCity,");
            strSql.Append("EmpArea,");
            strSql.Append("DepId,");
            strSql.Append("DepName,");
            strSql.Append("LevelId,");
            strSql.Append("LevelName,");
            strSql.Append("RoleId,");
            strSql.Append("RoleName,");
            strSql.Append("IsWork,");
            strSql.Append("Caption,");
            strSql.Append("WxOpenId,");
            strSql.Append("Status,");
            strSql.Append("AddTime,");
            strSql.Append("AddUser,");
            strSql.Append("LastEditTime,");
            strSql.Append("LastEditUser");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@CompanyId,");
            strSql.Append("@CompanyName,");
            strSql.Append("@CityId,");
            strSql.Append("@EmpNum,");
            strSql.Append("@EmpName,");
            strSql.Append("@EmpPwd,");
            strSql.Append("@EmpCard,");
            strSql.Append("@EmpBirthday,");
            strSql.Append("@EmpSex,");
            strSql.Append("@EmpAge,");
            strSql.Append("@EmpCulture,");
            strSql.Append("@EmpMobile,");
            strSql.Append("@UrgentName,");
            strSql.Append("@UrgentMobile,");
            strSql.Append("@EmpProvince,");
            strSql.Append("@EmpCity,");
            strSql.Append("@EmpArea,");
            strSql.Append("@DepId,");
            strSql.Append("@DepName,");
            strSql.Append("@LevelId,");
            strSql.Append("@LevelName,");
            strSql.Append("@RoleId,");
            strSql.Append("@RoleName,");
            strSql.Append("@IsWork,");
            strSql.Append("@Caption,");
            strSql.Append("@WxOpenId,");
            strSql.Append("@Status,");
            strSql.Append("@AddTime,");
            strSql.Append("@AddUser,");
            strSql.Append("@LastEditTime,");
            strSql.Append("@LastEditUser");
            strSql.Append("); ");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model);
            }
        }


        /// <summary>
        /// 员工删除
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <param name="LastEditUser"></param>
        /// <returns></returns>
        public bool DelB_EmployeeInfo(int EmployeeId, int LastEditUser)
        {
            string strSql = "UPDATE B_EmployeeInfo SET Status=1,LastEditTime=GETDATE(),LastEditUser=@LastEditUser WHERE EmployeeId=@EmployeeId";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), new { EmployeeId, LastEditUser }) > 0;
            }
        }


        public int EditB_EmployeeInfo(B_EmployeeInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE B_EmployeeInfo SET ");
            strSql.Append(" CompanyId=@CompanyId,");
            strSql.Append(" CompanyName=@CompanyName,");
            strSql.Append(" CityId=@CityId,");
            strSql.Append(" EmpNum=@EmpNum,");
            strSql.Append(" EmpName=@EmpName,");
            strSql.Append(" EmpCard=@EmpCard,");
            strSql.Append(" EmpBirthday=@EmpBirthday,");
            strSql.Append(" EmpSex=@EmpSex,");
            strSql.Append(" EmpAge=@EmpAge,");
            strSql.Append(" EmpCulture=@EmpCulture,");
            strSql.Append(" EmpMobile=@EmpMobile,");
            strSql.Append(" UrgentName=@UrgentName,");
            strSql.Append(" UrgentMobile=@UrgentMobile,");
            strSql.Append(" EmpProvince=@EmpProvince,");
            strSql.Append(" EmpCity=@EmpCity,");
            strSql.Append(" EmpArea=@EmpArea,");
            strSql.Append(" DepId=@DepId,");
            strSql.Append(" DepName=@DepName,");
            strSql.Append(" LevelId=@LevelId,");
            strSql.Append(" LevelName=@LevelName,");
            strSql.Append(" RoleId=@RoleId,");
            strSql.Append(" RoleName=@RoleName,");
            strSql.Append(" IsWork=@IsWork,");
            strSql.Append(" Caption=@Caption,");
            strSql.Append(" WxOpenId=@WxOpenId,");
            strSql.Append(" Status=@Status,");
            strSql.Append(" AddTime=@AddTime,");
            strSql.Append(" AddUser=@AddUser,");
            strSql.Append(" LastEditTime=@LastEditTime,");
            strSql.Append(" LastEditUser=@LastEditUser");
            strSql.Append(" WHERE EmployeeId=@EmployeeId");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model);
            }
        }

        public List<B_EmployeeInfo> GetB_EmployeeInfoList(List<int> empId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" EmployeeId");
            strSql.Append(",CityId");
            strSql.Append(",EmpNum");
            strSql.Append(",EmpName");
            strSql.Append(",EmpCard");
            strSql.Append(",EmpBirthday");
            strSql.Append(",EmpSex");
            strSql.Append(",EmpAge");
            strSql.Append(",EmpCulture");
            strSql.Append(",EmpMobile");
            strSql.Append(",UrgentName");
            strSql.Append(",UrgentMobile");
            strSql.Append(",EmpProvince");
            strSql.Append(",EmpCity");
            strSql.Append(",EmpArea");
            strSql.Append(",DepId");
            strSql.Append(",DepName");
            strSql.Append(",LevelId");
            strSql.Append(",LevelName");
            strSql.Append(",IsWork");
            strSql.Append(",Caption");
            strSql.Append(",WxOpenId");
            strSql.Append($" FROM B_EmployeeInfo WITH(NOLOCK) WHERE [Status]=0  AND EmployeeId IN({string.Join(",", empId)})");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<B_EmployeeInfo> list = conn.Query<B_EmployeeInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public List<B_EmployeeInfo> GetB_EmployeeInfoList(EmployeeInfoInput input, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = GetB_EmployeeInfoCount(input);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY EmployeeId DESC) AS ORDERBYID");
            strSql.Append(",CompanyId");
            strSql.Append(",CompanyName");
            strSql.Append(",EmployeeId");
            strSql.Append(",CityId");
            strSql.Append(",EmpNum");
            strSql.Append(",EmpName");
            strSql.Append(",EmpPwd");
            strSql.Append(",EmpCard");
            strSql.Append(",EmpBirthday");
            strSql.Append(",EmpSex");
            strSql.Append(",EmpAge");
            strSql.Append(",EmpCulture");
            strSql.Append(",EmpMobile");
            strSql.Append(",UrgentName");
            strSql.Append(",UrgentMobile");
            strSql.Append(",EmpProvince");
            strSql.Append(",EmpCity");
            strSql.Append(",EmpArea");
            strSql.Append(",DepId");
            strSql.Append(",DepName");
            strSql.Append(",LevelId");
            strSql.Append(",LevelName");
            strSql.Append(",RoleId");
            strSql.Append(",RoleName");
            strSql.Append(",IsWork");
            strSql.Append(",Caption");
            strSql.Append(",WxOpenId");
            strSql.AppendFormat(" FROM B_EmployeeInfo WITH(NOLOCK) WHERE [Status]=0 ");
            DynamicParameters p = new DynamicParameters();
            p.Add("@pageIndex", pageIndex);
            p.Add("@pageSize", pageSize);
            if (!string.IsNullOrEmpty(input.EmpNum))
            {
                strSql.Append(" AND EmpNum = @EmpNum");
                p.Add("EmpNum", input.EmpNum);
            }
            if (!string.IsNullOrEmpty(input.EmployeeName))
            {
                strSql.Append(" AND EmployeeName = @EmployeeName");
                p.Add("EmployeeName", input.EmployeeName);
            }
            if (!string.IsNullOrEmpty(input.EmpMobile))
            {
                strSql.Append(" AND EmpMobile = @EmpMobile");
                p.Add("EmpMobile", input.EmpMobile);
            }
            if (input.DepId > 1)
            {
                strSql.Append(" AND DepId = @DepId");
                p.Add("DepId", input.DepId);
            }
            if (input.LevelId > 0)
            {
                strSql.Append(" AND LevelId = @LevelId");
                p.Add("LevelId", input.LevelId);
            }
            strSql.Append(" ) AS T WHERE T.ORDERBYID  BETWEEN @pageIndex AND @pageSize ");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<B_EmployeeInfo> list = conn.Query<B_EmployeeInfo>(strSql.ToString(), p).ToList();
                return list;
            }
        }

        public int GetB_EmployeeInfoCount(EmployeeInfoInput input)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(*) FROM B_EmployeeInfo WITH(NOLOCK) WHERE [Status]=0 ");
            DynamicParameters p = new DynamicParameters();
            if (!string.IsNullOrEmpty(input.EmpNum))
            {
                strSql.Append(" AND EmpNum = @EmpNum");
                p.Add("EmpNum", input.EmpNum);
            }
            if (!string.IsNullOrEmpty(input.EmployeeName))
            {
                strSql.Append(" AND EmployeeName = @EmployeeName");
                p.Add("EmployeeName", input.EmployeeName);
            }
            if (!string.IsNullOrEmpty(input.EmpMobile))
            {
                strSql.Append(" AND EmpMobile = @EmpMobile");
                p.Add("EmpMobile", input.EmpMobile);
            }
            if (input.DepId > 1)
            {
                strSql.Append(" AND DepId = @DepId");
                p.Add("DepId", input.DepId);
            }
            if (input.LevelId > 0)
            {
                strSql.Append(" AND LevelId = @LevelId");
                p.Add("LevelId", input.LevelId);
            }
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString(), p);
                return obj.IntParse();
            }
        }

        public B_EmployeeInfo GetB_EmployeeInfoModel(int EmployeeId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("EmployeeId");
            strSql.Append(",CityId");
            strSql.Append(",EmpNum");
            strSql.Append(",EmpName");
            strSql.Append(",EmpPwd");
            strSql.Append(",EmpCard");
            strSql.Append(",EmpBirthday");
            strSql.Append(",EmpSex");
            strSql.Append(",EmpAge");
            strSql.Append(",EmpCulture");
            strSql.Append(",EmpMobile");
            strSql.Append(",UrgentName");
            strSql.Append(",UrgentMobile");
            strSql.Append(",EmpProvince");
            strSql.Append(",EmpCity");
            strSql.Append(",EmpArea");
            strSql.Append(",DepId");
            strSql.Append(",DepName");
            strSql.Append(",LevelId");
            strSql.Append(",LevelName");
            strSql.Append(",IsWork");
            strSql.Append(",Caption");
            strSql.Append(",WxOpenId");
            strSql.Append(",Status");
            strSql.Append(",AddTime");
            strSql.Append(",AddUser");
            strSql.Append(",LastEditTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",CompanyId");
            strSql.Append(" FROM B_EmployeeInfo WITH(NOLOCK) WHERE EmployeeId=@EmployeeId");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                B_EmployeeInfo model = conn.Query<B_EmployeeInfo>(strSql.ToString(), new { EmployeeId }).SingleOrDefault();
                return model;
            }
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <returns></returns>
        public int RestPassWord(RestPassWordInput input)
        {
            string strSql = @"
UPDATE  dbo.B_EmployeeInfo
SET     EmpPwd = @EmpPwd
WHERE   [Status] = 0
        AND EmployeeId = @EmployeeId";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql, input);
            }
        }

        public B_EmployeeInfo Login(string EmpMobile, string EmpPwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("EmployeeId");
            strSql.Append(",CityId");
            strSql.Append(",EmpNum");
            strSql.Append(",EmpName");
            strSql.Append(",EmpPwd");
            strSql.Append(",EmpCard");
            strSql.Append(",EmpBirthday");
            strSql.Append(",EmpSex");
            strSql.Append(",EmpAge");
            strSql.Append(",EmpCulture");
            strSql.Append(",EmpMobile");
            strSql.Append(",UrgentName");
            strSql.Append(",UrgentMobile");
            strSql.Append(",EmpProvince");
            strSql.Append(",EmpCity");
            strSql.Append(",EmpArea");
            strSql.Append(",DepId");
            strSql.Append(",DepName");
            strSql.Append(",LevelId");
            strSql.Append(",LevelName");
            strSql.Append(",RoleId");
            strSql.Append(",RoleName");
            strSql.Append(",IsWork");
            strSql.Append(",Caption");
            strSql.Append(",WxOpenId");
            strSql.Append(",Status");
            strSql.Append(",AddTime");
            strSql.Append(",AddUser");
            strSql.Append(",LastEditTime");
            strSql.Append(",LastEditUser");
            strSql.Append(@" FROM B_EmployeeInfo WITH(NOLOCK) WHERE  EmpMobile = @EmpMobile AND EmpPwd = @EmpPwd");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                B_EmployeeInfo model = conn.Query<B_EmployeeInfo>(strSql.ToString(), new { EmpMobile, EmpPwd }).SingleOrDefault();
                return model;
            }
        }

        /// <summary>
        /// 判断是否存在手机号码
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <param name="EmpMobile"></param>
        /// <returns></returns>
        public int IsExistsMobile(int EmployeeId, string EmpMobile)
        {
            string strSql = @"
SELECT  COUNT(*)
FROM    dbo.B_EmployeeInfo
WHERE   EmpMobile = @EmpMobile
        AND EmployeeId <> @EmployeeId
        AND [Status] = 0";

            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql, new { EmployeeId, EmpMobile });
                return obj.IntParse();
            }
        }


        /// <summary>
        /// 获取员工与仓库的关系
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <returns></returns>
        public List<int> GetUserWareHouseList(int EmployeeId)
        {
            string strSql = @"
SELECT  WareHouseId
FROM    dbo.LK_UserWareHouse WITH ( NOLOCK )
WHERE   UserId = @EmployeeId";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<int> list = conn.Query<int>(strSql, new { EmployeeId }).ToList();
                return list;
            }
        }


        /// <summary>
        /// 删除仓库与员工关系
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public int DelLK_UserWareHouse(int EmployeeId)
        {
            string strSql = "DELETE FROM LK_UserWareHouse WHERE UserId=@EmployeeId";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql, new { EmployeeId });
            }
        }

        /// <summary>
        /// 添加仓库与人员关系
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddLK_UserWareHouse(LKUserWareHouse model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO LK_UserWareHouse");
            strSql.Append("(");
            strSql.Append("UserId,");
            strSql.Append("WareHouseId");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@UserId,");
            strSql.Append("@WareHouseId");
            strSql.Append("); ");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model);
            }
        }

        /// <summary>
        /// 获取员工被分配的冷库
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public List<UserWareHouseVM> GetList(List<int> UserId)
        {
            string strSql = $@"
SELECT  WareHouseName ,
        UserId
FROM    LK_WareHouse WITH ( NOLOCK )
        LEFT JOIN dbo.LK_UserWareHouse WITH ( NOLOCK ) ON dbo.LK_WareHouse.Id = dbo.LK_UserWareHouse.WareHouseId
        WHERE UserId IN({string.Join(",", UserId)})
";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<UserWareHouseVM> list = conn.Query<UserWareHouseVM>(strSql).ToList();
                return list;
            }
        }


        /// <summary>
        /// 获取员工前10条数据
        /// </summary>
        /// <param name="EmpName"></param>
        /// <returns></returns>
        public List<B_EmployeeInfo> GetE_EmployeeInfoListLikeFront10(string EmpName)
        {
    
            string strSql = @"
SELECT TOP ( 10 )
        EmployeeId ,
        EmpName ,
        EmpNum ,
        EmpMobile ,
        DepName ,
        LevelName
FROM    dbo.B_EmployeeInfo WITH ( NOLOCK )
WHERE   IsWork = 1
        AND ( EmpName LIKE '%' + @EmpName + '%'
              OR EmpNum LIKE '%' + @EmpName + '%'
              OR EmpMobile LIKE '%' + @EmpName + '%'
            );";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<B_EmployeeInfo> list = conn.Query<B_EmployeeInfo>(strSql, new { EmpName }).ToList();
                return list;
            }
        }
    }
}
